package com.cac.demo.newCode.cemop.utils;

import com.cac.demo.newCode.cemop.dto.CemopLogDTO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@Component
public class SQLiteUtil {

    @Resource
    private Environment environment;



    public  void executeSql(String sql){
        Connection connection = null;
        try {
          String sqlUrl=environment.getProperty("cemopOrLocalLog.linuxFiledDir");
            connection = DriverManager.getConnection(sqlUrl);
            // 接下来你可以使用connection对象来执行SQL语句，例如创建表、查询数据等
            Statement statement = connection.createStatement();
            statement.execute("PRAGMA journal_mode = WAL;");
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null && !connection.isClosed()) {
                    // 关闭连接
                    connection.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public List<CemopLogDTO> executeSqlQuery(String sql){
        Connection connection = null;
        try {
            // 连接到SQLite数据库，数据库文件是test.db，如果文件不存在，会自动创建
            String sqlUrl=environment.getProperty("cemopOrLocalLog.linuxFiledDir");
            connection = DriverManager.getConnection(sqlUrl);
            // 接下来你可以使用connection对象来执行SQL语句，例如创建表、查询数据等
            Statement statement = connection.createStatement();
            statement.execute("PRAGMA journal_mode = WAL;");
            ResultSet resultSet = statement.executeQuery(sql);
            ArrayList<CemopLogDTO> cemopLogDTOS = new ArrayList<>();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String receiveDataUrl = resultSet.getString("receive_data_url");
                String receiveData = resultSet.getString("receive_data");
                String sendDataUrl = resultSet.getString("send_data_url");
                String sendData = resultSet.getString("send_data");
                String logType = resultSet.getString("log_type");
                String interfaceName = resultSet.getString("Interface_name");
                String logLevel = resultSet.getString("log_level");
                String logArea = resultSet.getString("log_area");
                String logPlatform = resultSet.getString("log_platform");
                String createDate = resultSet.getString("create_date");
                CemopLogDTO cemopLogDTO = new CemopLogDTO(id,receiveDataUrl,receiveData,sendDataUrl,sendData,logType,interfaceName,logLevel,logArea,logPlatform,createDate);
                cemopLogDTOS.add(cemopLogDTO);
            }
            return cemopLogDTOS;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null && !connection.isClosed()) {
                    // 关闭连接
                    connection.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return null;
    }

    public  String executeSqlCount(String sql) {
        Connection connection = null;
        try {
            // 连接到SQLite数据库，数据库文件是test.db，如果文件不存在，会自动创建
            String sqlUrl=environment.getProperty("cemopOrLocalLog.linuxFiledDir");
            connection = DriverManager.getConnection(sqlUrl);
            // 接下来你可以使用connection对象来执行SQL语句，例如创建表、查询数据等
            Statement statement = connection.createStatement();
            statement.execute("PRAGMA journal_mode = WAL;");
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                return resultSet.getString("pageTotal");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null && !connection.isClosed()) {
                    // 关闭连接
                    connection.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return null;
    }
}